import pymysql


class DAO_报录比:
    # 与数据库建立连接
    def get_conn(self):
        # 里皮的阿里云服务器
        # conn = pymysql.connect(host='39.105.39.215', port=3306, user="root", passwd="qq1040256886", db="yanxin", charset="utf8")

        # 本机的数据库
        conn = pymysql.connect(host='127.0.0.1', port=3306, user="root", passwd="root", db="yanxin", charset="utf8")

        return conn

    # 插入报录比到专业目标表（这个模块创建于2018-12-10）
    def add_报录比(self, res_year, res_register_num, res_enroll_num, res_recommend_num, res_uni_name, res_college_name, res_maj_name):
        # 打开数据库连接
        db = self.get_conn()
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 更新语句
        sql = "insert into research_direction(res_year, res_register_num, res_enroll_num, res_recommend_num, res_uni_name, res_college_name, res_maj_name)" \
              " values(%s, %d, %d ,'%s', '%s', '%s', '%s')" % (res_year, res_register_num, res_enroll_num, res_recommend_num, res_uni_name, res_college_name, res_maj_name)
        try:
            # 执行sql语句
            cursor.execute(sql)
            # 执行sql语句
            db.commit()
            print(res_uni_name, res_maj_name, res_year, "年报录比 insert ok")
        except:
            # 发生错误时回滚
            print("MMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMM")
            db.rollback()
        # 关闭数据库连接
        db.close()

    # 插入报录比到专业目标表（这个模块创建于2018-12-10）
    def add_报录比_name_way(self, res_year, res_register_num, res_enroll_num, res_recommend_num, res_uni_name, res_college_name, res_maj_name, res_name, res_way):
        # 打开数据库连接
        db = self.get_conn()
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 更新语句
        sql = "insert into research_direction(res_year, res_register_num, res_enroll_num, res_recommend_num, res_uni_name, res_college_name, res_maj_name, res_name, res_way)" \
              " values(%s, %d, %d ,'%s', '%s', '%s', '%s', '%s', '%s')" % (res_year, res_register_num, res_enroll_num, res_recommend_num, res_uni_name, res_college_name, res_maj_name, res_name, res_way)
        try:
            # 执行sql语句
            cursor.execute(sql)
            # 执行sql语句
            db.commit()
            print(res_uni_name, res_maj_name, res_year, "年报录比 insert ok")
        except:
            # 发生错误时回滚
            print("MMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMM")
            db.rollback()
        # 关闭数据库连接
        db.close()

    # 更新专业目标表的报录比（这个模块创建于2018-12-10）
    # def update_报录比(self, res_year, res_register_num, res_enroll_num, res_recommend_num, res_uni_name, res_college_name, res_maj_name):
    def update_报录比_多行更新(self,paras):

        # 打开数据库连接
        db = self.get_conn()
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 更新语句
        # sql = "update research_direction set res_register_num = %d, res_enroll_num = %d, res_recommend_num = %d where res_year = '%s' and res_uni_name = '%s' and res_college_name = '%s' and res_maj_name = '%s'" % (res_register_num, res_enroll_num, res_recommend_num, res_year, res_uni_name, res_college_name, res_maj_name)
        sql = "update research_direction set res_register_num = %s, res_enroll_num = %s, res_recommend_num = %s where res_year = %s and res_uni_name = %s and res_college_name = %s and res_maj_name = %s"
        # try:
        #     # 执行sql语句
        # paras=((res_register_num, res_enroll_num, res_recommend_num, res_year, res_uni_name, res_college_name, res_maj_name))
        rows = cursor.executemany(sql, paras)
        # rows = cursor.execute(sql)
        # 执行sql语句
        db.commit()
        # if rows == 0:
            # print(res_uni_name, res_maj_name, res_year, "年报录比 not update")
        # elif rows > 0:
            # print(res_uni_name, res_maj_name, res_year, "年报录比 update ok")
        print(rows)
        # except:
        #     # 发生错误时回滚
        #     print("MMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMM")
        #     db.rollback()
        # 关闭数据库连接
        db.close()
    #  单行更新
    def update_报录比_单行更新(self, res_year, res_register_num, res_enroll_num, res_recommend_num, res_uni_name, res_college_name, res_maj_name):
        # 打开数据库连接
        db = self.get_conn()
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 更新语句
        sql = "update research_direction set res_register_num = %d, res_enroll_num = %d, res_recommend_num = %d where res_year = '%s' and res_uni_name = '%s' and res_college_name = '%s' and res_maj_name = '%s'" % (res_register_num, res_enroll_num, res_recommend_num, res_year, res_uni_name, res_college_name, res_maj_name)
        # try:
        #     # 执行sql语句
        rows = cursor.execute(sql)
        # 执行sql语句
        db.commit()
        if rows == 0:
            print(res_uni_name, res_maj_name, res_year, "年报录比 not update")
        elif rows > 0:
            print(res_uni_name, res_maj_name, res_year, "年报录比 update ok")
        print(rows)
        # except:
        #     # 发生错误时回滚
        #     print("MMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMM")
        #     db.rollback()
        # 关闭数据库连接
        db.close()
